{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Executing JavaScript Code Directly in SQL Queries Using the `jseval` Function Tutorial\n",
    "\n",
    "MLDB provides a complete implementation of the [SQL](../../../../doc/#builtin/sql/Sql.md.html) SELECT statement. Most of the functions you are used to using [are available](../../../../doc/#builtin/sql/ValueExpression.md.html) in your queries. \n",
    "\n",
    "MLDB also supports additional functions that extend standard SQL in very interesting ways. One of those function is the [`jseval` function](../../../../doc/#builtin/sql/ValueExpression.md.html#jseval) that can be used to execute arbitrary JavaScript code inline in an SQL query."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this tutorial, we will show some basic usage example followed by two different use-cases for the `jseval` function:\n",
    "\n",
    "- Formatting data during the import step\n",
    "- Designing custom feature generators\n",
    "\n",
    "### Setting up\n",
    "\n",
    "Before we begin, let's start by importing the `pymldb` library so we can make [REST API](../../../../doc/#builtin/WorkingWithRest.md.html) calls to MLDB. You can check out the [Using `pymldb` Tutorial](../../../../doc/nblink.html#_tutorials/Using pymldb Tutorial) for more details."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pymldb import Connection\n",
    "mldb = Connection(\"http://localhost\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Basic usage examples\n",
    "\n",
    "Let's start by writing a simple SQL query that will multiply an input number by 2 in JavaScript:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>output</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>result</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          output\n",
       "_rowName        \n",
       "result        10"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "    SELECT\n",
    "        jseval('\n",
    "            return val * 2;\n",
    "        ','val', 5) AS output\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The variable `val` takes the input value 5 and the code is then evaluated.\n",
    "\n",
    "Our function can also take in multiple parameters as input, and return different output values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>output.mult</th>\n",
       "      <th>output.name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>result</th>\n",
       "      <td>10</td>\n",
       "      <td>Bonjour! Hello!</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          output.mult      output.name\n",
       "_rowName                              \n",
       "result             10  Bonjour! Hello!"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "    SELECT\n",
    "        jseval('\n",
    "            var output = {};\n",
    "            output[\"mult\"] = val * 2;\n",
    "            output[\"name\"] = str_val + \" Hello!\";\n",
    "            return output;\n",
    "        ','val,str_val', 5, 'Bonjour!') AS output\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the above example, the string `val,str_val` mean that the function takes 2 input variables. Those values will be 5 and the string `Bonjour!`. Since we return a JavaScript [object](http://www.w3schools.com/js/js_objects.asp), we essentially return a row where the keys are the objects' keys and the cell values are the object's values.\n",
    "\n",
    "Now that we have the basics in place, let's continue to a real use-case below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Formatting data during the import step"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the [Loading Data From An HTTP Server Tutorial](../../../../ipy/notebooks/_tutorials/_latest/Loading%20Data%20From%20An%20HTTP%20Server%20Tutorial.ipynb) tutorial, we loaded a specific file from an archive that was located on the [Stanford Network Analysis Project (SNAP)](http://snap.stanford.edu/index.html) website. \n",
    "\n",
    "The dataset contains all the circles of friends in which user no. 3980 is part of. Each row represents a circle of \n",
    "friends, and all the users that are part of that circle will be enumerated on the line.\n",
    "\n",
    "Let's check's out the unformated version of the data first, by running the `import.text` procedure:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>\"circle0\t3989\t4009\"</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>circle1\\t4010\\t4037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>circle2\\t4013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>circle3\\t4024\\t3987\\t4015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>circle4\\t4006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>circle5\\t4035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>circle6\\t3999\\t4028\\t4005\\t3996\\t4031\\t4018\\t3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>circle7\\t3984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>circle8\\t3988\\t4030\\t4026\\t4021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>circle9\\t3983\\t3992\\t4033\\t4017\\t4000\\t3986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>circle10\\t3990\\t4007\\t4016\\t4025</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      \"circle0\\t3989\\t4009\"\n",
       "_rowName                                                   \n",
       "2                                       circle1\\t4010\\t4037\n",
       "3                                             circle2\\t4013\n",
       "4                                 circle3\\t4024\\t3987\\t4015\n",
       "5                                             circle4\\t4006\n",
       "6                                             circle5\\t4035\n",
       "7         circle6\\t3999\\t4028\\t4005\\t3996\\t4031\\t4018\\t3...\n",
       "8                                             circle7\\t3984\n",
       "9                           circle8\\t3988\\t4030\\t4026\\t4021\n",
       "10              circle9\\t3983\\t3992\\t4033\\t4017\\t4000\\t3986\n",
       "11                         circle10\\t3990\\t4007\\t4016\\t4025"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataUrl = \"http://snap.stanford.edu/data/facebook.tar.gz\"\n",
    "\n",
    "mldb.put(\"/v1/procedures/import_data\", {\n",
    "    \"type\": \"import.text\",\n",
    "    \"params\": {\n",
    "        \"dataFileUrl\": \"archive+\" + dataUrl + \"#facebook/3980.circles\",\n",
    "        \"delimiter\": \" \", \n",
    "        \"quoteChar\": \"\",\n",
    "        \"outputDataset\": \"import_URL2\",\n",
    "        \"runOnCreation\": True\n",
    "    }\n",
    "})\n",
    "\n",
    "mldb.query(\"SELECT * NAMED rowName() FROM import_URL2 LIMIT 10\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that each line contains the circle number followed by user ids. This type of data is an ideal candidate for MLDB, since we can store it as [*bags of words*](https://en.wikipedia.org/wiki/Bag-of-words_model), or rather, *bags of friends*. A dataset of type [sparse.mutable](../../../../doc/#builtin/datasets/MutableSparseMatrixDataset.md.html) can store sparse representations like this one very efficiently.\n",
    "\n",
    "Normally, we could use the `tokenize` function to deal with data like this. However, since splitting the data on the `<TAB>` character yields a variable number of columns, the standard way of importing this won't work very nicely in the `import.text` procedure.\n",
    "\n",
    "In the code below, we will use the [`jseval` function](../../../../doc/#builtin/sql/ValueExpression.md.html#jseval) to do the following in JavaScript:\n",
    "- create an empty [object](http://www.w3schools.com/js/js_objects.asp)\n",
    "- split each line on the `<TAB>` character\n",
    "- store the first element of each line under the key `rowName` in the object (circle0, circle1, etc...)\n",
    "- store all remaining elements of the line using the element's name as the key, and the number 1 as the value\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "dataUrl = \"http://snap.stanford.edu/data/facebook.tar.gz\"\n",
    "\n",
    "print mldb.put(\"/v1/procedures/import_non_formated\", {\n",
    "    \"type\": \"import.text\",\n",
    "    \"params\": {\n",
    "        \"dataFileUrl\": \"archive+\" + dataUrl + \"#facebook/3980.circles\",\n",
    "        \"headers\": [\"circles\"],\n",
    "        \"select\": \"\"\"\n",
    "            jseval('\n",
    "                var row_val = val.split(\"\\t\");\n",
    "                var rtn = {};\n",
    "                rtn[\"rowName\"] = row_val[0];\n",
    "                for(i=1; i<row_val.length; i++) {\n",
    "                    rtn[row_val[i]] = 1;\n",
    "                }\n",
    "                return rtn;\n",
    "                ','val', circles) AS *\n",
    "        \"\"\",\n",
    "        \"outputDataset\": {\n",
    "            \"id\": \"import_non_formated\",\n",
    "            \"type\": \"sparse.mutable\"\n",
    "        },\n",
    "        \"runOnCreation\": True\n",
    "    }\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now run a `SELECT` query on the resulting dataset and get a nice sparse representation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>3989</th>\n",
       "      <th>4009</th>\n",
       "      <th>4010</th>\n",
       "      <th>4037</th>\n",
       "      <th>4013</th>\n",
       "      <th>3987</th>\n",
       "      <th>4015</th>\n",
       "      <th>4024</th>\n",
       "      <th>4006</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>circle0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>circle1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>circle2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>circle3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>circle4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          3989  4009  4010  4037  4013  3987  4015  4024  4006\n",
       "_rowName                                                      \n",
       "circle0      1     1   NaN   NaN   NaN   NaN   NaN   NaN   NaN\n",
       "circle1    NaN   NaN     1     1   NaN   NaN   NaN   NaN   NaN\n",
       "circle2    NaN   NaN   NaN   NaN     1   NaN   NaN   NaN   NaN\n",
       "circle3    NaN   NaN   NaN   NaN   NaN     1     1     1   NaN\n",
       "circle4    NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN     1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "    SELECT * EXCLUDING(rowName)\n",
    "    NAMED rowName\n",
    "    FROM import_non_formated \n",
    "    ORDER BY CAST(rowName() AS integer) \n",
    "    LIMIT 5\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now answer a simple question like: *Is there any friend of user 3980 that appears in more than one of his circle of friends?* It can be answered with the following query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4030</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4013</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4020</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4023</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3999</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          result\n",
       "_rowName        \n",
       "4030           1\n",
       "4013           1\n",
       "4020           1\n",
       "4023           1\n",
       "3999           1"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "    SELECT *\n",
    "    FROM transpose(\n",
    "        (\n",
    "            SELECT sum({* EXCLUDING(rowName)}) as * \n",
    "            NAMED 'result'\n",
    "            FROM import_non_formated\n",
    "        )\n",
    "    )\n",
    "    ORDER BY result DESC\n",
    "    LIMIT 5\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the maximum value is 1, we now know that the answer to the above question is no.\n",
    "\n",
    "Although there are other ways to obtain the same result, using `jseval` and the dataset of type `sparse.mutable` allowed us to transform our data in a single step, without knowing its characteristics in advance. This shows how much added flexibility is added by such a function."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Designing custom feature generators\n",
    "\n",
    "Another very powerful way the `jseval` function can be used is as a feature generator. When trying to prototype and iterate quickly, this can be a very efficient way to try out new ideas.\n",
    "\n",
    "Let's start by creating a toy dataset using the description of machine learning concepts from [Wikipedia](https://www.wikipedia.org):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put('/v1/procedures/import_ML_concepts', {\n",
    "        \"type\":\"import.text\",\n",
    "        \"params\": {\n",
    "            \"dataFileUrl\":\"http://public.mldb.ai/datasets/MachineLearningConcepts.csv\",\n",
    "            \"outputDataset\": \"ml_concepts\",\n",
    "            \"named\": \"Concepts\",\n",
    "            \"select\": \"Text\",\n",
    "            \"runOnCreation\": True\n",
    "        }\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Taking a peek at our data, we see there is a single column called `Text` that contains a textual description of an ML concept:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Text</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Artificial neural network</th>\n",
       "      <td>In machine learning and cognitive science, art...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Autoencoder</th>\n",
       "      <td>An autoencoder, autoassociator or Diabolo netw...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hopfield network</th>\n",
       "      <td>A Hopfield network is a form of recurrent arti...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Boltzmann machine</th>\n",
       "      <td>Boltzmann machine is a type of stochastic recu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Restricted boltzmann machines</th>\n",
       "      <td>A restricted Boltzmann machine (RBM) is a gene...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Deep belief network</th>\n",
       "      <td>In machine learning, a deep belief network (DB...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Logistic regression</th>\n",
       "      <td>In statistics, logistic regression, or logit r...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Naive bayes classifier</th>\n",
       "      <td>In machine learning, naive Bayes classifiers a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Support vector machine</th>\n",
       "      <td>In machine learning, support vector machines (...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                            Text\n",
       "_rowName                                                                        \n",
       "Artificial neural network      In machine learning and cognitive science, art...\n",
       "Autoencoder                    An autoencoder, autoassociator or Diabolo netw...\n",
       "Hopfield network               A Hopfield network is a form of recurrent arti...\n",
       "Boltzmann machine              Boltzmann machine is a type of stochastic recu...\n",
       "Restricted boltzmann machines  A restricted Boltzmann machine (RBM) is a gene...\n",
       "Deep belief network            In machine learning, a deep belief network (DB...\n",
       "Logistic regression            In statistics, logistic regression, or logit r...\n",
       "Naive bayes classifier         In machine learning, naive Bayes classifiers a...\n",
       "Support vector machine         In machine learning, support vector machines (..."
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"SELECT * FROM ml_concepts\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now create a function of type [`sql.expression`](../../../../doc/#builtin/functions/SqlExpressionFunction.md.html) containing a `jseval` function that calculates different statistics about the string it is given. It calculates things like the number of words in the string, the number of capital letters, etc.\n",
    "\n",
    "Putting it in an `sql.expression` allows us to reuse it easily later on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put(\"/v1/functions/getStats\", {\n",
    "    \"type\": \"sql.expression\",\n",
    "    \"params\": {\n",
    "        \"expression\": \"\"\"\n",
    "            jseval(' \n",
    "                var result = {};\n",
    "\n",
    "                result[\"len\"] = txt.length;\n",
    "                result[\"numWords\"] = txt.split(\" \").length;\n",
    "                result[\"numCapital\"] = txt.replace(/[^A-Z]/g, \"\").length;\n",
    "                result[\"numExpl\"] = txt.replace(/[^!]/g, \"\").length;\n",
    "                result[\"numQst\"] = txt.replace(/[^?]/g, \"\").length;\n",
    "                result[\"containsHashSign\"] = txt.replace(/[^#]/g, \"\").length >= 1;\n",
    "                result[\"numNumbers\"] = txt.replace(/[^0-9]/g, \"\").length;\n",
    "\n",
    "                result[\"capitalProportion\"] = result[\"numCapital\"] / result[\"len\"];\n",
    "                result[\"explProportion\"] = result[\"numExpl\"] / result[\"len\"];\n",
    "                result[\"qstProportion\"] = result[\"numQst\"] / result[\"len\"];\n",
    "                result[\"numberProportion\"] = result[\"numNumbers\"] / result[\"len\"];\n",
    "\n",
    "                return result;\n",
    "            ', 'txt', text) as stats\n",
    "        \"\"\"\n",
    "    }\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have created our `getStats` function, we can call it on a single string:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stats.capitalProportion</th>\n",
       "      <th>stats.containsHashSign</th>\n",
       "      <th>stats.explProportion</th>\n",
       "      <th>stats.len</th>\n",
       "      <th>stats.numCapital</th>\n",
       "      <th>stats.numExpl</th>\n",
       "      <th>stats.numNumbers</th>\n",
       "      <th>stats.numQst</th>\n",
       "      <th>stats.numWords</th>\n",
       "      <th>stats.numberProportion</th>\n",
       "      <th>stats.qstProportion</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>result</th>\n",
       "      <td>0.028571</td>\n",
       "      <td>true</td>\n",
       "      <td>0</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          stats.capitalProportion stats.containsHashSign  \\\n",
       "_rowName                                                   \n",
       "result                   0.028571                   true   \n",
       "\n",
       "          stats.explProportion  stats.len  stats.numCapital  stats.numExpl  \\\n",
       "_rowName                                                                     \n",
       "result                       0         35                 1              0   \n",
       "\n",
       "          stats.numNumbers  stats.numQst  stats.numWords  \\\n",
       "_rowName                                                   \n",
       "result                   0             0               6   \n",
       "\n",
       "          stats.numberProportion  stats.qstProportion  \n",
       "_rowName                                               \n",
       "result                         0                    0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"SELECT getStats({text: 'This is a test #hopethisworks #mldb'}) as *\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like it works! We can also call it on the `Text` column of our `ml_concepts` dataset to get the statistics for all the rows of our dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stats.capitalProportion</th>\n",
       "      <th>stats.containsHashSign</th>\n",
       "      <th>stats.explProportion</th>\n",
       "      <th>stats.len</th>\n",
       "      <th>stats.numCapital</th>\n",
       "      <th>stats.numExpl</th>\n",
       "      <th>stats.numNumbers</th>\n",
       "      <th>stats.numQst</th>\n",
       "      <th>stats.numWords</th>\n",
       "      <th>stats.numberProportion</th>\n",
       "      <th>stats.qstProportion</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Artificial neural network</th>\n",
       "      <td>0.012469</td>\n",
       "      <td>false</td>\n",
       "      <td>0</td>\n",
       "      <td>401</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>60</td>\n",
       "      <td>0.002494</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Autoencoder</th>\n",
       "      <td>0.010582</td>\n",
       "      <td>false</td>\n",
       "      <td>0</td>\n",
       "      <td>378</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>54</td>\n",
       "      <td>0.018519</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hopfield network</th>\n",
       "      <td>0.016461</td>\n",
       "      <td>false</td>\n",
       "      <td>0</td>\n",
       "      <td>486</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>73</td>\n",
       "      <td>0.020576</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Boltzmann machine</th>\n",
       "      <td>0.015890</td>\n",
       "      <td>false</td>\n",
       "      <td>0</td>\n",
       "      <td>944</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>140</td>\n",
       "      <td>0.005297</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Restricted boltzmann machines</th>\n",
       "      <td>0.020817</td>\n",
       "      <td>false</td>\n",
       "      <td>0</td>\n",
       "      <td>1249</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>180</td>\n",
       "      <td>0.011209</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Deep belief network</th>\n",
       "      <td>0.021021</td>\n",
       "      <td>false</td>\n",
       "      <td>0</td>\n",
       "      <td>999</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>155</td>\n",
       "      <td>0.003003</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Logistic regression</th>\n",
       "      <td>0.010979</td>\n",
       "      <td>false</td>\n",
       "      <td>0</td>\n",
       "      <td>1093</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>158</td>\n",
       "      <td>0.006404</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Naive bayes classifier</th>\n",
       "      <td>0.016049</td>\n",
       "      <td>false</td>\n",
       "      <td>0</td>\n",
       "      <td>1620</td>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>238</td>\n",
       "      <td>0.011728</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Support vector machine</th>\n",
       "      <td>0.012709</td>\n",
       "      <td>false</td>\n",
       "      <td>0</td>\n",
       "      <td>1495</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>236</td>\n",
       "      <td>0.001338</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               stats.capitalProportion stats.containsHashSign  \\\n",
       "_rowName                                                                        \n",
       "Artificial neural network                     0.012469                  false   \n",
       "Autoencoder                                   0.010582                  false   \n",
       "Hopfield network                              0.016461                  false   \n",
       "Boltzmann machine                             0.015890                  false   \n",
       "Restricted boltzmann machines                 0.020817                  false   \n",
       "Deep belief network                           0.021021                  false   \n",
       "Logistic regression                           0.010979                  false   \n",
       "Naive bayes classifier                        0.016049                  false   \n",
       "Support vector machine                        0.012709                  false   \n",
       "\n",
       "                               stats.explProportion  stats.len  \\\n",
       "_rowName                                                         \n",
       "Artificial neural network                         0        401   \n",
       "Autoencoder                                       0        378   \n",
       "Hopfield network                                  0        486   \n",
       "Boltzmann machine                                 0        944   \n",
       "Restricted boltzmann machines                     0       1249   \n",
       "Deep belief network                               0        999   \n",
       "Logistic regression                               0       1093   \n",
       "Naive bayes classifier                            0       1620   \n",
       "Support vector machine                            0       1495   \n",
       "\n",
       "                               stats.numCapital  stats.numExpl  \\\n",
       "_rowName                                                         \n",
       "Artificial neural network                     5              0   \n",
       "Autoencoder                                   4              0   \n",
       "Hopfield network                              8              0   \n",
       "Boltzmann machine                            15              0   \n",
       "Restricted boltzmann machines                26              0   \n",
       "Deep belief network                          21              0   \n",
       "Logistic regression                          12              0   \n",
       "Naive bayes classifier                       26              0   \n",
       "Support vector machine                       19              0   \n",
       "\n",
       "                               stats.numNumbers  stats.numQst  stats.numWords  \\\n",
       "_rowName                                                                        \n",
       "Artificial neural network                     1             0              60   \n",
       "Autoencoder                                   7             0              54   \n",
       "Hopfield network                             10             0              73   \n",
       "Boltzmann machine                             5             0             140   \n",
       "Restricted boltzmann machines                14             0             180   \n",
       "Deep belief network                           3             0             155   \n",
       "Logistic regression                           7             0             158   \n",
       "Naive bayes classifier                       19             0             238   \n",
       "Support vector machine                        2             0             236   \n",
       "\n",
       "                               stats.numberProportion  stats.qstProportion  \n",
       "_rowName                                                                    \n",
       "Artificial neural network                    0.002494                    0  \n",
       "Autoencoder                                  0.018519                    0  \n",
       "Hopfield network                             0.020576                    0  \n",
       "Boltzmann machine                            0.005297                    0  \n",
       "Restricted boltzmann machines                0.011209                    0  \n",
       "Deep belief network                          0.003003                    0  \n",
       "Logistic regression                          0.006404                    0  \n",
       "Naive bayes classifier                       0.011728                    0  \n",
       "Support vector machine                       0.001338                    0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"SELECT getStats({text: Text}) as * FROM ml_concepts\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Doing most of this is possible in standard SQL, but the `jseval` implementation is simple, fast and compact. This is a great way to quickly experiment with ideas and gives maximum flexibility to manipulate data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Where to next?\n",
    "\n",
    "Check out the other [Tutorials and Demos](../../../../doc/#builtin/Demos.md.html)."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
